package com.jkj.utils;

import java.io.FileInputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="data"></param>
    /// <param name="headDict"></param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public static <T> XSSFWorkbook exportExcel(List<T> data, Map<String, String> headMap, String sheetName, boolean showSrNo)
            throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        XSSFWorkbook wb = new XSSFWorkbook();
        if (StrUtil.isEmpty(sheetName)) {
            sheetName = "sheet1";
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        List<String> headList = new ArrayList<String>();
        if (showSrNo) {
            headList.add("RowNum");
        }
        for (Entry<String, String> entry : headMap.entrySet()) {
            headList.add(entry.getKey());
        }
        // 添加头部
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            if (showSrNo && headList.get(i).equals("RowNum")) {
                cell.setCellValue("序号");
            } else {
                cell.setCellValue(headMap.get(headList.get(i)));
            }
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
        }
        // 循环添加内容
        for (int i = 0; i < data.size(); i++) {
            T entity = data.get(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < headList.size(); j++) {
                XSSFCell cell = row.createCell(j);
                if (showSrNo && headList.get(j).equals("RowNum")) {
                    cell.setCellValue(i);
                } else {
                    Method met = entity.getClass().getMethod("get" + initStr(headList.get(j)));
                    Object value = met.invoke(entity);
                    if (value instanceof Boolean)
                        cell.setCellValue((Boolean) value);
                    else if (value instanceof Calendar)
                        cell.setCellValue((Calendar) value);
                    else if (value instanceof Date)
                        cell.setCellValue((Date) value);
                    else if (value instanceof Double)
                        cell.setCellValue((Double) value);
                    else if (value instanceof String)
                        cell.setCellValue((String) value);
                    else
                        cell.setCellValue(value.toString());
                }
                cell.setCellStyle(style);
                sheet.autoSizeColumn(j);
            }
        }
        return wb;
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="data"></param>
    /// <param name="headDict"></param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    public static XSSFWorkbook exportExcel2(List<Map<String, Object>> data, Map<String, String> headMap, String sheetName, boolean showSrNo) throws IllegalArgumentException {
        XSSFWorkbook wb = new XSSFWorkbook();
        if (StrUtil.isEmpty(sheetName)) {
            sheetName = "sheet1";
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        List<String> headList = new ArrayList<String>();
        if (showSrNo) {
            headList.add("RowNum");
        }
        for (Entry<String, String> entry : headMap.entrySet()) {
            headList.add(entry.getKey());
        }

        // 添加头部
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = row.createCell(i);
            if (showSrNo && headList.get(i).equals("RowNum")) {
                cell.setCellValue("序号");
            } else {
                cell.setCellValue(headMap.get(headList.get(i)));
            }
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 循环添加内容
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> entity = data.get(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < headList.size(); j++) {
                XSSFCell cell = row.createCell(j);
                if (showSrNo && headList.get(j).equals("RowNum")) {
                    cell.setCellValue(i);
                } else {
                    Object value = entity.get(headList.get(j));
                    if (value instanceof Boolean)
                        cell.setCellValue((boolean) value + "");
                    else if (value instanceof Calendar)
                        cell.setCellValue(((Calendar) value).toString());
                    else if (value instanceof Date)
                        cell.setCellValue(dateFormat.format((Date) value));
                    else if (value instanceof Double)
                        cell.setCellValue((double) value + "");
                    else if (value instanceof String)
                        cell.setCellValue((String) value);
                    else if (value instanceof Integer)
                        cell.setCellValue((int) value + "");
                    else
                        cell.setCellValue(value.toString());
                }
                cell.setCellStyle(style);
                sheet.autoSizeColumn(j);
            }
        }
        return wb;
    }


    public static String initStr(String old) { // 将单词的首字母大写
        String str = old.substring(0, 1).toUpperCase() + old.substring(1);
        return str;
    }


    public static List<Map<String, Object>> getDataFromExcel(String filePath, int colCount) throws Exception {
        //判断是否为excel类型文件
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            System.out.println("文件不是excel类型");
        }
        FileInputStream fis = null;
        Workbook wookbook = null;
        //获取一个绝对地址的流
        fis = new FileInputStream(filePath);
        //2007版本的excel，用.xlsx结尾
        wookbook = new XSSFWorkbook(fis);//得到工作簿
        //要获得属性
        String name = "";
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得表头
        Row rowHead = sheet.getRow(0);
        List<String> headList = new ArrayList<String>();
        for (int i = 0; i < colCount; i++) {
            Cell cell = rowHead.getCell(i);
            name = cell.getStringCellValue();
            headList.add(name);
        }
        List<Map<String, Object>> list = new ArrayList<>();
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        //获得所有数据
        for (int i = 1; i <= totalRowNum; i++) {
            Map<String, Object> map = new LinkedHashMap<>();
            //获得第i行对象
            Row row = sheet.getRow(i);
            for (int j = 0; j < headList.size(); j++) {
                //获得获得第i行第0列的 String类型对象
                Cell cell = row.getCell(j);
                CellType cellType = cell.getCellTypeEnum();
                if (cellType == CellType.STRING)
                    name = cell.getStringCellValue();
                if (cellType == CellType.NUMERIC)
                    name = cell.getNumericCellValue() + "";
                if (cellType == CellType.BLANK)
                    name = "";
                map.put(headList.get(j), name);
            }
            list.add(map);
        }
        return list;
    }
}
